﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Data;
using MaxLeaf.DB;
using MaxLeaf.Common;
using System.IO;
using System.Data.Common;
namespace MaxLeaf.DAL
{
    public  class UpdateOnHandOverNightDAL
    {
        public bool UpdateOnHand()
        {
            string strSQL = "";
            DataTable dtAvail = new DataTable();
            ServerInfo serverMaster = ServerInfo.GetK3MasterServer();

            using (DBAccess mycon = new DBAccess(serverMaster))
            {
                strSQL = " select FItemID from t_item where fitemclassid=4 ";
                dtAvail = mycon.GetDataTable(strSQL);

                foreach (DataRow drRow in dtAvail.Rows)
                {
                    double dblQty = 0;
                    string strMasterFitemID = CvtFunc.ToString(drRow["FItemID"]) ;
                    string strFitemID = strMasterFitemID; 
                    foreach (ServerInfo server in ServerInfo.GetServers(ServerType.All))
                    {
                        if (server.Type != ServerType.Web)
                        {
                            using (DBAccess con = new DBAccess(server))
                            {
                                if (!server.MasterDB) strFitemID = new K3.ImInvMasterDAL().GetDestMapKeyValue(con, mycon, strMasterFitemID);
                                GetOnHand(ref dblQty,con, strFitemID);
                            }
                        }
                    }

                    foreach (ServerInfo server in ServerInfo.GetServers(ServerType.All))
                    {
                        if (server.Type == ServerType.Web)
                        {
                            using (DBAccess con = new DBAccess(server))
                            {
                                dblQty = GetRate(con, strMasterFitemID); 
                                if (!UpdateOneData(con,dblQty,strMasterFitemID))
                                {
                                    return true;
                                }
                            }
                        }
                    }
                }
            }
            return true;
        }

        private void GetOnHand(ref double dblQty, DBAccess dbcon , string strFitemID)
        {
           string strSQL = @"select sum(t1.Fqty),t3.fitemid
                            from icinventory t1 
                            inner join t_item t2 on t1.fstockid=t2.fitemid
                            inner join t_item t3 on t1.fitemid=t3.fitemid
                            where t2.fitemclassid=5 and t3.fitemclassid=4 and t3.fitemid='"+ strFitemID +"'";
           strSQL +=" group by t3.fitemid ";
           DataRow drQty = dbcon.GetDataFirstRow(strSQL);
           if (drQty != null)
           {
               dblQty += dblQty;
           }           
        }

        private double GetRate(DBAccess dbcon, string strItemItemID)
        {
            ServerInfo server = ServerInfo.GetK3MasterServer();
            DBAccess dbK3MasterCon = new DBAccess(server);
            
            double dblRate = 1;
            string strItemID = strItemItemID; //new K3.ImInvMasterDAL().GetDestMapKeyValue(dbK3MasterCon, dbcon, strItemItemID);
                        
            string strSQL=" select  B.attr_value from mary_goods  A inner join mary_goods_attr B on A.goods_id=B.goods_id ";
            strSQL += " inner join mary_attribute C on B.attr_id=C.attr_id and C.attr_name='销售单位'  ";
            strSQL += " where FItemID='" + strItemID + "' order by A.goods_id";
            string strUnit = dbcon.GetValue(strSQL);
            if (strUnit == "") return 1;
            
            strSQL = " select B.Fmeasureunitid,b.Fcoefficient from t_icItem A inner join t_measureunit B on A.FunitGroupID= B.FunitGroupID and ";
            strSQL += " A.FItemID='" + strItemID + "'  ";   //and A.Fname='" + strItemName + "' ";
            strSQL += " and B.FName='" + strUnit + "'";
            DataRow drRate = dbcon.GetDataFirstRow(strSQL);
            if (drRate != null)
            {
                dblRate = CvtFunc.ToDouble(drRate["Fcoefficient"]);
            }
            return dblRate; 
        }

        public bool UpdateOneData(DBAccess mycon,double dblQty , string strMasterFitemID)
        {
            try
            {
                string strSQL = "update mary_goods set goods_number_new='"+ dblQty +"' where FItemID='"+ strMasterFitemID +"'";
                mycon.ExecuteSql( strSQL);
            }
            catch (Exception ex)
            {
                LogManager.Error(ex);
                return false;
            }
            return true;
        }


        public string GetOnHand(DBAccess dbCon, string strDBName, string strCustFitemID, string strItemItemID,string strUnit)
        {
            ServerInfo server = ServerInfo.GetK3MasterServer();
            DBAccess dbK3MasterCon = new DBAccess(server);

            string strReturn = "";
            string strItemFnumber = "";
            string strItemName = "";
            string strSQL = "select Fnumber from t_item where FItemID='" + strCustFitemID + "' and fitemclassid=1";
            string strCustNo = dbCon.GetValue(strSQL);

            string strItemID = new K3.ImInvMasterDAL().GetDestMapKeyValue(dbK3MasterCon, dbCon, strItemItemID);
            strSQL = "select Fnumber,FName from t_item where FItemID='" + strItemID + "' and fitemclassid=4";
            DataRow drItem = dbCon.GetDataFirstRow(strSQL);
            if (drItem != null)
            {
                strItemFnumber = CvtFunc.ToString(drItem["Fnumber"]);
                strItemName = CvtFunc.ToString(drItem["FName"]); 
            }
            else
            {
                return "0@0";
            }
            double dblRate =1;
            strSQL = " select A.*,B.Fmeasureunitid,b.Fcoefficient from t_icItem A inner join t_measureunit B on A.FunitGroupID= B.FunitGroupID and ";
            strSQL += " A.FItemID='" + strItemID + "' and A.Fname='" + strItemName + "' ";
            strSQL += " and B.FName='" + strUnit + "'";
            DataRow drRate = dbCon.GetDataFirstRow(strSQL);
            if (drRate != null)
            {
                dblRate = CvtFunc.ToDouble(drRate["Fcoefficient"]);
            }
            strSQL = @"select t1.Fqty
                            from icinventory t1 
                            inner join t_item t2 on t1.fstockid=t2.fitemid
                            inner join t_item t3 on t1.fitemid=t3.fitemid
                            where t2.fitemclassid=5 and t3.fitemclassid=4
                            and t2.fnumber=substring('{0}',3,6) and t3.fnumber='{1}'";
            strSQL = string.Format(strSQL, strCustNo, strItemFnumber);

            DataRow dr = dbCon.GetDataFirstRow(strSQL);
            if (dr != null)
            {
                strReturn = CvtFunc.ToString ( CvtFunc.ToDouble(dr["Fqty"])/dblRate );
            }
            else
            {
                strReturn = "0";
            }
            strSQL = @"select sum(t1.Fqty)  as Fqty
                            from icinventory t1 
                            inner join t_item t2 on t1.fstockid=t2.fitemid
                            inner join t_item t3 on t1.fitemid=t3.fitemid
                            where t2.fitemclassid=5 and t3.fitemclassid=4
                            and t3.fnumber='{0}'
                            group by t3.fnumber";
            strSQL = string.Format(strSQL, strItemFnumber);
            dr = dbCon.GetDataFirstRow(strSQL);
            if (dr != null)
            {
                strReturn += "@" + CvtFunc.ToString(CvtFunc.ToDouble(dr["Fqty"]) / dblRate);
            }
            else
            {
                strReturn += "@0";
            }
            return strReturn;
        }

    }
}
